#!/usr/bin/python3
# -*- coding: utf-8 -*-

from sqlalchemy import (
    MetaData, Table, Column, ForeignKey,
    Integer, String, DateTime, Boolean, Text, Float
)
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from settings import config
from datetime import datetime

import logging

Base = declarative_base()
DSN = "sqlite:///{db_file}?check_same_thread=False"

class User(Base):
    __tablename__='user'
    id = Column(Integer, primary_key=True)
    # 指定name映射到name字段; name字段为字符串类形，
    name = Column(String(20))
    email = Column(String(20))
    password = Column(String(50))
    image = Column(String(500))
    # 是否为管理员
    admin = Column(Boolean)
    created_at = Column(DateTime, default=datetime.now())
    updated_at = Column(DateTime)

    def __repr__(self):
        return "[User(id='%s', name='%s', email='%s', admin='%s', image='%s', created_at='%s', updated_at='%s')]" % (self.id, self.name, self.email, self.admin, self.image, self.created_at, self.updated_at)

class Blog(Base):
    __tablename__='blogs'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    user_name =  Column(String(20))
    user_image = Column(String(500))
    name = Column(String(50))
    summary = Column(String(200))
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.now())
    updated_at = Column(DateTime)

    def __repr__(self):
        return "[Blog(id='%s', user_id='%s', user_name='%s', user_image='%s', name='%s', summary='%s', content='%s', created_at='%s', updated_at='%s')]" % (self.id, self.user_id, self.user_name, self.user_image, self.name, self.summary, self.content, self.created_at, self.updated_at)

class Comment(Base):
    __tablename__='comments'

    id = Column(Integer, primary_key=True)
    blog_id = Column(Integer)
    user_id = Column(Integer)
    user_name =  Column(String(20))
    user_image = Column(String(500))
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.now())
    updated_at = Column(DateTime)

    def __repr__(self):
        return "[Comment(id='%s', blog_id='%s', user_id='%s', user_name='%s', user_image='%s', content='%s', created_at='%s', updated_at='%s')]" % (self.id, self.blog_id, self.user_id, self.user_name, self.user_image, self.content, self.created_at, self.updated_at)

    # 单个对象方法2
    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}
        
    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

class Clipboard(Base):
    __tablename__='clipboard'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    user_name =  Column(String(20))
    user_image = Column(String(500))
    content = Column(Text)
    created_at = Column(DateTime, default=datetime.now())
    updated_at = Column(DateTime)

    def __repr__(self):
        return "[Clipboard(id='%s', user_id='%s', user_name='%s', user_image='%s', content='%s', created_at='%s', updated_at='%s')]" % (self.id, self.user_id, self.user_name, self.user_image, self.content, self.created_at, self.updated_at)
 
    # 单个对象方法2
    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}
        
    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

class Fdfs(Base):
    __tablename__='Fdfs'

    id = Column(Integer, primary_key=True)
    group_name = Column(String(20))
    filename = Column(String(100))
    file_id =  Column(String(100))
    status = Column(Boolean)
    size = Column(Float())
    ip = Column(String(50))
    path = Column(String(512))
    referer = Column(String(512))
    content_type = Column(String(20))
    download_count = Column(Integer)
    created_at = Column(DateTime, default=datetime.now())
    updated_at = Column(DateTime)

    def __repr__(self):
        return "[Fdfs(id='%s', group_name='%s', filename='%s', file_id='%s', status='%s', size='%s', ip='%s', path='%s', referer='%s', content_type='%s', download_count='%s', created_at='%s', updated_at='%s')]" % (self.id, self.group_name, self.filename, self.file_id, self.status, self.size, self.ip, self.path, self.referer, self.content_type, self.download_count, self.created_at, self.updated_at)
    # 单个对象方法2
    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}
        
    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

def create_tables():
    global engine
    Base.metadata.create_all(engine)

def sample_data(session):
    session.add_all(
        [User(name='shadaileng', email='shadaileng@gmail.com', admin=True, password='7294c433ac7436a38436ae355997bf1e5f2ecd31')]
    )
    session.add_all(
        [Fdfs(status=True, filename='/root', content_type='d')]
    )
    session.commit()

def create_session():
    global engine
    # engine是2.2中创建的连接
    Session = sessionmaker(bind=engine)
    # 创建Session类实例
    session = Session()
    return session

async def init_db(app):
    global engine
    conf = app['config']['sqlite']
    logging.info('[+] connect to db file %s' % conf)
    db_url = DSN.format(**conf)
    engine = create_engine(db_url, echo=True)
    app['db'] = engine

def init_db_():
    global engine
    conf = config['sqlite']
    print('[+] connect to db file %s' % conf)
    db_url = DSN.format(**conf)
    engine = create_engine(db_url, echo=True)

async def close_db(app):
    app['db'].close()
    await app['db'].wait_closed()

if __name__ == '__main__':
    init_db_()
    session = create_session()

    create_tables()
    sample_data(session)
    # session.query(User).delete()
    # session.query(Blog).delete()
    # session.query(Clipboard).delete()
    # session.commit()
    # user = session.query(User).filter_by(id='1').first()
    # print(user)
    # for user in session.query(User):
    #     print(user)
